import openpyxl
import threading
from pathlib import Path


class MyExcelTool(object):
    
    __instance_lock = threading.Lock()

    def __init__(self):
        import openpyxl
        self.openpyxl = openpyxl
        self.wb = None

    def __new__(cls,*args,**kwargs):
        "单例模式"
        if not hasattr(cls, '_instance'):
            with XL.__instance_lock:
                if not hasattr(cls, '_instance'):
                    XL._instance = super().__new__(cls)
            return XL._instance
    
    def __del__(self):
        self.wb.close()

    def createXL(self,wb_path,sh_name="sheet1"):
        self.wb = self.openpyxl.Workbook()
        self.wb.path = wb_path
        self.sh = self.wb[sh_name]
        return self.wb
    
    def loadXL(self,wb_path):
        self.load_path = wb_path
        self.wb = self.openpyxl.load_workbook(wb_path)
        return self.wb
    
    def chooseXL(self,sh_name="Sheet1"):
        self.sh = self.wb[sh_name]

    def splitXL(self,column=1,title_rows=1,sh_name="Sheet1",):
        "以第N列的值为组，拆分成不同的EXCLE，并以该值命名文件。"
        self.chooseXL(sh_name="Sheet1")
        # 1、该值去重，得到val_set
        # 2、以val_set的每一项为文件名，单例的建立文件，并单例录入信息，并单例保存。
        # 3、以val_set的每一项为文件名，建立所有文件，并同步录入信息，并同步保存。
        val_set = set()
        for line in list(self.sh)[title_rows:]:
            if line[column-1].value:
                val_set.add(line[column-1].value)
            elif not line[column-1].value:
                val_set.add(line[column-1].value)
        print(val_set)
        
        for set_item in val_set:
            wb = openpyxl.Workbook()
            sh = wb.create_sheet("Sheet1",0)
            count = 0
            valid_data = 0

            for line in list(self.sh):
                count += 1
                row_list = []
                for cell in line:
                    row_list.append(cell.value)
                if count <= title_rows:
                    sh.append(row_list)
                elif line[column-1].value == set_item:
                    valid_data += 1
                    sh.append(row_list)
            
            parent_path = Path(self.load_path).parent
            source_filename = str(self.load_path).split("/")[-1][:-5]
            # file_name = "离退休人员设备清单-%s-%s台件.xlsx" % (set_item,valid_data)
            file_name = "%s-%s台件.xlsx" % (set_item.replace("/","--"),valid_data)

            file_full_path = Path(parent_path/source_filename/file_name)
            file_full_path.parent.mkdir(parents=True,exist_ok=True)
            print("保存至：" + str(file_full_path))
            wb.save(file_full_path)
            
            wb.close()
            print("已完成", set_item)d
            



"""
0、指定需要拆分的Excel路径
excel_path = Path("C:\\Users\\Administrator\\Desktop\\test.xlsx")

# 1、引入该库
import MyExcelTool

# 2、实例化MyExcelTool对象
MyExcel = MyExcelTool()

# 3、加载指定路径的Excel
MyExcel = loadXL(excel_path)

# 4、使用splitXL方法，拆分Excel表格，并按该
# splitXL(column=1,title_rows=1,sh_name="Sheet1",)
# column：代表列数，从1计数。
# title_rows：标题行数，新生成的Excel使用源文档的标题，指定标题行数。从1计数。
MyExcel.splitXL(4)

# 执行结果是：
    1、在该文档同级别的目录下，新建与文档同名的文件夹。
    2、并指定列的值为文件名，将该列所有相同的数据，放入该列名的文档中。
"""